package com.eascs.web.o2o.web.bi.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.web.o2o.web.bi.dao.BIDao;
import com.eascs.web.o2o.web.bi.entity.BILoanDetail;
import com.eascs.web.o2o.web.bi.entity.BILoanInfo;
import com.eascs.web.o2o.web.bi.vo.LoanDetailForm;

/**
 * 放款信息
 * 
 * @author ycz
 *
 */
@Service
public class LoanInfoService {
	@Autowired
	private BIDao biDao;

	public List<BILoanInfo> queryLoanInfo() {
		String sql = "SELECT	t.regional_name AS provincial,	(a.loanAmount) AS loanBalance,	c.monthLoanAmount AS loanMonthBalance," + "	d.yearLoanAmout AS loanYearBalance,	g.fiveDaysDue AS fiveDaysDue,	h.fiveTenDaysDue AS fiveTenDaysDue"
				+ " FROM o2o_platform_regional t LEFT JOIN (	SELECT	t.regional_name,g.loanAmount FROM o2o_platform_regional t" + "	LEFT JOIN (	SELECT	sum(c.outstanding_of_deposits) AS loanAmount,p.regional_name FROM 	o2o_bank_loan_data f"
				+ "	LEFT JOIN o2o_loanapply y ON y.bill_code = f.bill_code   LEFT JOIN o2o_after_loan_for_billcode c ON f.bill_code=c.bill_code" + "	LEFT JOIN o2o_platform_regional p ON p.platform_id = y.ltd	WHERE	f.is_del = 'N' GROUP BY	p.regional_name	) g "
				+ " ON g.regional_name = t.regional_name GROUP BY t.regional_name) a ON a.regional_name = t.regional_name " + " LEFT JOIN (	SELECT	p.regional_name,	sum(f.loan_amount) AS monthLoanAmount	FROM o2o_bank_loan_data f"
				+ "	LEFT JOIN o2o_loanapply y ON y.bill_code = f.bill_code	LEFT JOIN o2o_platform_regional p ON p.platform_id = y.ltd" + "	WHERE	f.is_del = 'N'	AND f.loan_date >= DATE_ADD(curdate(),INTERVAL - DAY (curdate()) + 1 DAY)"
				+ "	AND f.loan_date <= sysdate() GROUP BY	p.regional_name) c ON c.regional_name = t.regional_name" + " LEFT JOIN (	SELECT	p.regional_name,	sum(f.loan_amount) AS yearLoanAmout	FROM	o2o_bank_loan_data f"
				+ "	LEFT JOIN o2o_loanapply y ON y.bill_code = f.bill_code LEFT JOIN o2o_platform_regional p ON p.platform_id = y.ltd" + "	WHERE	f.is_del = 'N'	AND f.loan_date >= DATE_SUB(CURDATE(),INTERVAL dayofyear(	date_format(sysdate(), '%y-%m-%d')) - 1 DAY"
				+ "	)	AND f.loan_date <= sysdate()	GROUP BY	p.regional_name) d ON d.regional_name = t.regional_name" + " LEFT JOIN (	SELECT	p.regional_name,	SUM(f.loan_amount) AS fiveDaysDue	FROM	o2o_bank_loan_data f"
				+ "	LEFT JOIN o2o_loanapply y ON y.bill_code = f.bill_code	LEFT JOIN o2o_platform_regional p ON p.platform_id = y.ltd" + "	WHERE	DATE_ADD(	f.loan_date,INTERVAL f.loan_term MONTH	) BETWEEN date_format(SYSDATE(), '%y-%m-%d')"
				+ "	AND date_add(	date_format(sysdate(), '%y-%m-%d'),	INTERVAL 5 DAY)	GROUP BY	p.regional_name" + " ) g ON g.regional_name = t.regional_name LEFT JOIN (	SELECT	p.regional_name,	SUM(f.loan_amount) AS fiveTenDaysDue FROM	"
				+ "	o2o_bank_loan_data f LEFT JOIN o2o_loanapply y ON y.bill_code = f.bill_code LEFT JOIN o2o_platform_regional p " + " ON p.platform_id = y.ltd	WHERE	DATE_ADD(	f.loan_date,INTERVAL f.loan_term MONTH) BETWEEN date_add(date_format(sysdate(), '%y-%m-%d'),INTERVAL 5 DAY)	"
				+ " AND date_add(	date_format(sysdate(), '%y-%m-%d'),	INTERVAL 10 DAY	) GROUP BY	p.regional_name) h ON h.regional_name = t.regional_name GROUP BY t.regional_name";
		return biDao.findEntitys(sql, BILoanInfo.class);
	}

	/**
	 * 放款明细
	 * 
	 * @param LoanDetailForm
	 * @return
	 */
	public List<BILoanDetail> findLoanDetailByPage(LoanDetailForm loanDetailForm) {
		StringBuffer sql = new StringBuffer();
		sql.append(
				" SELECT oll.loanapply_no AS physicalId, oll.bill_code AS billCode, oll.loan_id AS customerId, CONCAT( LEFT (olu.user_full_name, 1), '**' ) AS customerName, opr.province_name AS provincial, CONCAT( oll.ltd_code, '-', oll.ltd_name ) AS ltd, o.item_name AS loanBank, ( SELECT item_name FROM pub_dditem WHERE dd_no = '11321' AND item_code = oll.loan_type ) AS loanType, CAST( ROUND(( CASE WHEN ( CASE WHEN (oll.bank_amount) IS NULL THEN oll.ea_mount ELSE (oll.bank_amount) END ) IS NULL THEN oll.loana_mount ELSE oll.ea_mount END ), 2 ) AS DECIMAL (10, 2)) AS loanAmount, DATE_FORMAT( oll.payment_time, '%Y-%m-%d' ) AS loanDate, DATE_FORMAT( oll.end_bank_date, '%Y-%m-%d' ) AS endBankDate FROM o2o_loanapply oll LEFT JOIN o2o_loan_user olu ON oll.loanapply_no = olu.loanapply_no AND oll.loan_id = olu.loan_id LEFT JOIN o2o_bank_loan_data loan ON oll.bill_code = loan.bill_code LEFT JOIN o2o_platform_regional opr ON oll.ltd = opr.platform_id LEFT JOIN ( SELECT o.item_name, o.item_code FROM pub_dditem o WHERE o.dd_no = 10340 GROUP BY o.item_code ) o ON loan.loan_bank = o.item_code WHERE oll.loan_flg = 'Y' AND loan.is_del = 'N' AND olu.sponsor_type IS NULL AND olu.spouse_id IS NULL ");

		if (StringUtil.hasText(loanDetailForm.getStartDate())) {
			sql.append(" and oll.payment_time >= ");
			sql.append("'");
			sql.append(loanDetailForm.getStartDate());
			sql.append("'");
		}
		if (StringUtil.hasText(loanDetailForm.getEndDate())) {
			sql.append(" and oll.payment_time <  ");
			sql.append("'");
			sql.append(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(loanDetailForm.getEndDate()), 1)));
			sql.append("'");
		}

		sql.append(" order by oll.payment_time desc ");
		return biDao.findEntitys(sql.toString(), BILoanDetail.class);
	}
}
